/**
 * 
 */
package excelframe;

import java.awt.Color;
import java.awt.EventQueue;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.filechooser.FileNameExtensionFilter;

import parse.ExportExcel;
import parse.ParseExcelToBean;
import parse.RowVO;
import db.DBUtils;

/**
 * <p>Title:ExcelParse</p>
 * <p>Description:TODO</p>
 * @author	xiongf
 * @date	2016年1月25日 下午1:11:07
 * @version 1.0
 */
public class ExcelParse implements ActionListener {

	private JFrame frm;

	private JTextField textField;

	private String textFieldVal;

	public static JButton btnNewButton_1;

	public static JLabel lblNewLabel_1 = new JLabel("");

	public JFrame getFrm() {
		return frm;
	}

	public void setFrm(JFrame frm) {
		this.frm = frm;
	}

	public JTextField getTextField() {
		return textField;
	}

	public void setTextField() {
		this.textField = new JTextField();
	}

	public String getTextFieldVal() {
		return textFieldVal;
	}

	public void setTextFieldVal(String textFieldVal) {
		this.textFieldVal = textFieldVal;
	}

	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {

			public void run() {
				try {
					ExcelParse window = new ExcelParse();
					window.frm.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the application.
	 */
	public ExcelParse() {
		initialize();
	}

	/**
	 * Initialize the contents of the frame.
	 */
	private void initialize() {
		frm = new JFrame();
		frm.setTitle("EBICS下传对账单账号维护");
		frm.setBounds(100, 100, 758, 446);
		frm.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		frm.getContentPane().setLayout(null);

		JButton btnNewButton = new JButton("选择excel");
		btnNewButton.setBounds(505, 310, 109, 32);
		frm.getContentPane().add(btnNewButton);
		btnNewButton.setVisible(false);

		btnNewButton.addActionListener(this);
		textField = new JTextField();
		textField.setEditable(false);
		textField.setBounds(208, 310, 263, 32);
		frm.getContentPane().add(textField);
		textField.setColumns(10);
		textField.setVisible(false);

		JLabel lblNewLabel = new JLabel("excel路径:");
		lblNewLabel.setFont(new Font("宋体", Font.BOLD, 16));
		lblNewLabel.setBounds(52, 312, 118, 28);
		lblNewLabel.setVisible(false);
		frm.getContentPane().add(lblNewLabel);

		btnNewButton_1 = new JButton("数据导入");
		btnNewButton_1.setBounds(257, 240, 158, 34);
		frm.getContentPane().add(btnNewButton_1);
		btnNewButton_1.setEnabled(true);

		lblNewLabel_1.setFont(new Font("宋体", Font.BOLD, 14));
		lblNewLabel_1.setBounds(15, 330, 711, 60);
		lblNewLabel_1.setForeground(Color.red);
		frm.getContentPane().add(lblNewLabel_1);

		JButton btnNewButton_2 = new JButton("数据导出");
		btnNewButton_2.setBounds(257, 162, 158, 34);
		frm.getContentPane().add(btnNewButton_2);

		JTextArea lblNewLabel_2 = new JTextArea(
		        "备注:“数据导出”按钮用于将已签约数据从数据表中(RELE_TD_CUSM_SIGN)导出为excel文档，“数据导入”按钮用于将数据从excel文档中导入到签约表中。"
		                + "本工具导入导出都是全量，请注意导入时需要在已维护数据的基础上进行编辑修改再进行导入，否则将造成数据丢失!");
		lblNewLabel_2.setAutoscrolls(true);
		lblNewLabel_2.setLineWrap(true);
		lblNewLabel_2.setEditable(false);
		lblNewLabel_2.setBounds(15, 16, 706, 96);
		frm.getContentPane().add(lblNewLabel_2);
		lblNewLabel_2.setForeground(Color.RED);

		// 导入数据到数据库表
		btnNewButton_1.addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				try {
					String excelFilePath = new ExcelFrame().getFilePath();
					setTextFieldVal(excelFilePath);
					textField.setText(excelFilePath);
					if (null != excelFilePath && excelFilePath.length() != 0) {
						String filePath = getTextFieldVal().trim();
						List<List<RowVO>> excelSheets;

						excelSheets = ParseExcelToBean.excleTOVO(new File(filePath));
						try {
							if (null != excelSheets) {
								for (int i = 0; i < excelSheets.size(); i++) {
									List<RowVO> sheet = excelSheets.get(i);
									for (int j = 0; j < sheet.size(); j++) {
										RowVO commonVO = sheet.get(j);

										if (j == 0) {
											DBUtils.delSign();
										}
										DBUtils.addSign(commonVO);

									}
									lblNewLabel_1.setForeground(Color.black);
									String content = " upload success!"
									        .concat("you can export excel to check.");

									lblNewLabel_1.setText(filePath + content);
									// JOptionPane.showMessageDialog(null,
									// "ebics service success!");
									JOptionPane.showMessageDialog(null, "excel upload success!",
									        "提示", JOptionPane.PLAIN_MESSAGE);
								}
							}
						} catch (Exception e1) {
							JOptionPane.showMessageDialog(null, "excel upload error!", "错误",
							        JOptionPane.ERROR_MESSAGE);
							lblNewLabel_1.setForeground(Color.RED);
							lblNewLabel_1.setText(e1.getMessage());
						}
						// btnNewButton_1.setEnabled(false);
					}
				} catch (Exception e3) {
					lblNewLabel_1.setForeground(Color.RED);
					JOptionPane.showMessageDialog(null, "excel upload error!", "错误",
					        JOptionPane.ERROR_MESSAGE);
					lblNewLabel_1.setText(e3.getMessage());
				}

			}
		});

		// 数据导出为excel
		btnNewButton_2.addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				
				// 测试学生
				ExportExcel<RowVO> ex = new ExportExcel<RowVO>();
				String[] headers = { "客户号", "账号" };
				List<RowVO> dataset = new ArrayList<RowVO>();

				try {
					JFileChooser chooser = new JFileChooser();
					chooser.setApproveButtonText("导出");
					JPanel parent = new JPanel();
					int returnVal = chooser.showOpenDialog(parent);
					// 过滤Excel文件，只寻找以xls结尾的Excel文件
					FileNameExtensionFilter filter = new FileNameExtensionFilter("excel Files",
					        "xls");
					// chooser.setFileFilter(filter);
					/*if (returnVal != JFileChooser.APPROVE_OPTION) {
						JOptionPane.showMessageDialog(null, "文件后缀不为.xls,暂只支持2003版本excel");
					}*/
					if (returnVal == JFileChooser.APPROVE_OPTION) {
						dataset = DBUtils.getSign();
						String outFileName = chooser.getSelectedFile().getPath();
						if (!outFileName.endsWith(".xls")) {
							outFileName = chooser.getSelectedFile().getPath() + ".xls";
						}
						lblNewLabel_1.setForeground(Color.BLACK);
						lblNewLabel_1.setText("export file：" + outFileName);
						OutputStream out = new FileOutputStream(outFileName);
						ex.exportExcel(headers, dataset, out);
						out.close();
						// JOptionPane.showMessageDialog(null,
						// "excel download success!");
						JOptionPane.showMessageDialog(null, "excel download success!", "提示",
						        JOptionPane.PLAIN_MESSAGE);
						System.out.println("excel download success!");
					}if (returnVal == JFileChooser.CANCEL_OPTION) {
						lblNewLabel_1.setForeground(Color.BLACK);
						lblNewLabel_1.setText("");
					}
					
				} catch (Exception e1) {
					lblNewLabel_1.setForeground(Color.red);
					JOptionPane.showMessageDialog(null, "excel download error!", "错误",
					        JOptionPane.ERROR_MESSAGE);
					lblNewLabel_1.setText(e1.getMessage());
				}

			}
		});
	}

	public void actionPerformed(ActionEvent e) {
		String filePath = new ExcelFrame().getFilePath();
		this.setTextFieldVal(filePath);
		textField.setText(filePath);
		lblNewLabel_1.setText("");
		if (filePath != null) {
			btnNewButton_1.setEnabled(true);
		}
	}
}
